/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import ftpdownload.LogFile;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import pojo.Provider;

/**
 *
 * @author Mostafa
 */
public class ProviderDao {

    private Connection c;

    public ProviderDao(Connection c) {
        this.c = c;
    }

    public void insert(Provider p) {
        try {

            PreparedStatement ps = c.prepareStatement("insert into providers (name , type , headers ,table_name ,script_path) values ( ? ,? , ? , ? ,?)");

            ps.setString(1, p.getName());
            ps.setString(2, p.getType());
            ps.setString(3, p.getHeaders());
            ps.setString(4, p.getTableName());
            ps.setString(5, p.getScriptPath());

            ps.execute();

        } catch (SQLException ex) {
            Logger.getLogger(ProviderDao.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    public Provider getById(Integer id) {
        try {
            Provider p = new Provider();
            PreparedStatement ps = c.prepareStatement("select * from providers where p_id=?");
            ps.setInt(1, id);
            ResultSet rset = ps.executeQuery();
            rset.next();
            p.setPId(rset.getInt(1));
            p.setName(rset.getString(2));
            p.setType(rset.getString(3));
            p.setHeaders(rset.getString(4));
            p.setTableName(rset.getString(5));
            p.setScriptPath(rset.getString(6));

            return p;
        } catch (SQLException ex) {
            Logger.getLogger(ProviderDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    //method to get Provider Data by his Name
    public Provider getByName(String name) {
        try {
            Provider p = new Provider();
            PreparedStatement ps = c.prepareStatement("select * from providers where name=?");
            ps.setString(1, name);
            ResultSet rset = ps.executeQuery();
            if (rset.next()) {
                p.setPId(rset.getInt(1));
                p.setName(rset.getString(2));
                p.setType(rset.getString(3));
                p.setHeaders(rset.getString(4));
                p.setTableName(rset.getString(5));
                p.setScriptPath(rset.getString(6));
                return p;
            }
            else{
                LogFile.log(" System ", " did not find any Provider with Name ["+name+"]");
                return null;
            }
        } catch (SQLException ex) {
            LogFile.log(" System ", " encountered the following Exception while trying to retreive data of Provider : ["+name+"] ,the Exception is : ["+ex+"]");
            Logger.getLogger(ProviderDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public void update(Provider p) {
        try {
            boolean first = false;
            String updatetxt = "";
            if (p.getName() != null) {
                first = true;
                updatetxt = "name='" + p.getName() + "'";
            }
            if (p.getType() != null) {
                if (first) {
                    updatetxt += ", ";
                }
                first = true;
                updatetxt += "type='" + p.getType() + "'";
            }
            if (p.getHeaders() != null) {
                if (first) {
                    updatetxt += ", ";
                }
                first = true;
                updatetxt += "headers='" + p.getHeaders() + "'";
            }
            if (p.getTableName() != null) {
                if (first) {
                    updatetxt += ", ";
                }
                first = true;
                updatetxt += "table_name='" + p.getTableName() + "'";
            }
            if (p.getScriptPath() != null) {

                if (first) {
                    updatetxt += ", ";
                }
                first = true;
                updatetxt += "script_path='" + p.getScriptPath() + "'";
            }

            PreparedStatement ps = c.prepareStatement("update providers set " + updatetxt + " where p_id=?");
            ps.setInt(1, p.getPId());
            ps.executeUpdate();

        } catch (SQLException ex) {
            Logger.getLogger(ProviderDao.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    public void deleteByName(String name) {
        try {
            PreparedStatement ps = c.prepareStatement("delete from providers where name=?");
            ps.setString(1, name);
            ps.execute();
        } catch (SQLException ex) {
            Logger.getLogger(ProviderDao.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void deleteById(Integer id) {
        try {
            PreparedStatement ps = c.prepareStatement("delete from providers where p_id=?");
            ps.setInt(1, id);
            ps.execute();
        } catch (SQLException ex) {
            Logger.getLogger(ProviderDao.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static void main(String[] args) {
        try {
            String url = "jdbc:mysql://localhost:3306/dimensionproject";
            DriverManager.registerDriver(new com.mysql.jdbc.Driver());
            Connection c = DriverManager.getConnection(url, "root", "root");
            Provider p = new Provider();

            p.setName("mohsen");
            p.setType("book");
            new ProviderDao(c).insert(p);
            System.out.println("insert .. . . .");



        } catch (SQLException ex) {
            Logger.getLogger(ProviderDao.class.getName()).log(Level.SEVERE, null, ex);
        }

    }
}
